package dao;

import bean.EmpInfo;
import db.DBManager;
import sun.security.pkcs11.Secmod;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class EmpInfoDAO {
    private Connection conn;
    private PreparedStatement ps;
    private ResultSet rs;

    public List<EmpInfo> findAllEmp(){
        List<EmpInfo> list = new ArrayList<>();
        String sql = "select * from emp where empstate=1";
        conn = DBManager.getConnection();
        try {
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()){
                EmpInfo ei = new EmpInfo();
                ei.setEmpNo(rs.getInt(1));
                ei.setEname(rs.getString(2));
                ei.setJob(rs.getString(3));
                ei.setMgr(rs.getInt(4));
                ei.setHireDate(rs.getString(5));
                ei.setSal(rs.getDouble(6));
                ei.setComm(rs.getDouble(7));
                ei.setDeptNo(rs.getInt(8));
                ei.setEmpState(1);
                list.add(ei);
            }
        }catch (SQLException e){
            e.printStackTrace();
        }
        DBManager.closeConnection(conn,ps,rs);
        return list;
    }
    public void deleteEmp(int empNo){
        String sql = "update emp set empstate=0 where empno=?";
        conn = DBManager.getConnection();
        try {
            ps = conn.prepareStatement(sql);
            ps.setInt(1,empNo);
            ps.executeUpdate();
        }catch (SQLException e){
            System.out.println(e.getMessage());
        }
        DBManager.closeConnection(conn,ps,rs);
    }
    public List<EmpInfo> findEmpByPage(int start,int size){
        List<EmpInfo> list = new ArrayList<>();
        String sql = "select * from emp where empstate=1 limit ?,?";
        conn = DBManager.getConnection();
        try {
            ps = conn.prepareStatement(sql);
            ps.setInt(1,start);
            ps.setInt(2,size);
            rs = ps.executeQuery();
            while (rs.next()){
                EmpInfo ei = new EmpInfo();
                ei.setEmpNo(rs.getInt(1));
                ei.setEname(rs.getString(2));
                ei.setJob(rs.getString(3));
                ei.setMgr(rs.getInt(4));
                ei.setHireDate(rs.getString(5));
                ei.setSal(rs.getDouble(6));
                ei.setComm(rs.getDouble(7));
                ei.setDeptNo(rs.getInt(8));
                ei.setEmpState(1);
                list.add(ei);
            }
        }catch (SQLException e){
            e.printStackTrace();
        }
        DBManager.closeConnection(conn,ps,rs);
        return list;
    }
    public int findEmpAllCount(){
        String sql = "select count(empno) from emp where empstate=1";
        int count = 0;
        conn = DBManager.getConnection();
        try {
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            if (rs.next()){
                count = rs.getInt(1);
            }
        }catch (SQLException e){
            e.printStackTrace();
        }
        DBManager.closeConnection(conn,ps,rs);
        return count;
    }

}
